Option Explicit On

Imports System.Text

Public Class People

    Public Structure PersonInfo
        Public PersonId As Long
        Public Surname As String
        Public Firstname As String
        Public Title As String
        Public JobTitle As String
        Public Qualification As String
        Public Salary As String
        Public DOB As Date
        Public PassportNo As String
        Public PassportExp As Date
        Public DestinationId As Integer
        Public DestinationName As String
        Public NationalityId As Integer
        Public NationalityName As String
        Public ResidenceId As Integer
        Public ResidenceName As String
        Public UKVisaId As Integer
        Public UKVisaName As String
        Public UKVisaExpiry As Date
        Public AccountManagerId As Integer
        Public WhereHeard As String
        Public CompanyRoleId As Integer
        Public CompanyId As Integer
        Public WebUserName As String
        Public WebPassword As String
        Public WebAccess As Integer
        Public SkilledOccupationId As Integer
        Public SkillOccupationName As String
        Public EnableLogin As String
    End Structure

    Public Structure AddressInfo
        Public AddressId As Long
        Public AddressLinkId As Long
        Public AddressName As String
        Public AddressText As String
        Public Region As String
        Public Postcode As String
        Public CountryId As Integer
        Public CountryName As String
        Public AddressType As AddressTypes
        Public LinkId As Long 'Either person id or company id
    End Structure

    Public Structure ParentInfo
        Public ParentId As Long
        Public ParentName As String
    End Structure

    Public Enum PhoneTypes
        PhoneOther = 0
        PhoneMain = 1
        PhoneFax = 2
        PhoneBoth = 3
    End Enum

    Public Enum AddressTypes
        NotSet = 0
        Person = 1
        Company = 2
    End Enum

    Public Enum AddressLinkTypes
        NotSet = 0
        Correspondence = 1
        Invoice = 2
        Both = 3
    End Enum


    Public Shared Function AddAdvertising(ByVal WhereHeard As String, ByVal ConName As String, ByVal PersonId As Long) As Long

        'Every time a person is created we ask them where they heard about us

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Try

        
            With SQL
                .Append("INSERT INTO D_Advertising (Advertising_WhereHear, Advertising_EnteredBy, Advertising_LinkID, Advertising_LinkType)")
                .Append(" VALUES ('" & commonFunc.SwapQuotes(WhereHeard) & "','" & commonFunc.SwapQuotes(ConName) & "'," & PersonId & ",0)")
            End With

            Return DB.ExecuteSQL(SQL.ToString, True)
            'Return DB.GetIdentity
            'End If
        Catch ex As Exception
            Return 0
        Finally
            DB = Nothing
            commonFunc = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function UpdateAdvertising(ByVal LinkId As Long, ByVal WhereHeard As String) As Boolean

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()

        With SQL
            .Append("Update D_Advertising Set Advertising_WhereHear = '" & commonFunc.SwapQuotes(WhereHeard.Trim) & "'")
            .Append(" Where Advertising_LinkType = 0 ")
            .Append(" And Advertising_LinkID = " & LinkId)
        End With
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.ExecuteSQL(SQL.ToString)

    End Function

    Public Shared Function AddPerson(ByVal Person As PersonInfo, ByVal OldPerson As PersonInfo) As Long

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()

        If Person.PersonId = 0 Then

            With SQL
                .Append("INSERT INTO D_Person (Person_FName,Person_EnteredBy,Person_DTEntered, Person_Lname, Person_Title,")
                .Append("Person_NatID,Person_ResidentID,Person_UKVisaID,Person_DestinationID, Person_Jobtitle, Person_salary, Person_Qualification, Person_PassportNo,")
                .Append("person_dob,person_expirydt,person_PassportDTexpiry,Person_CompanyID, Person_CompanyRoleID,")
                .Append("Person_AccountManager) values (")

                .Append("'" & commonFunc.SwapQuotes(Person.Firstname) & "',")
                .Append(GlobalCRM.CurrentConID & ",")
                .Append("'" & commonFunc.FormatDate(Now, True) & "',")
                .Append("'" & commonFunc.SwapQuotes(Person.Surname) & "',")
                .Append("'" & commonFunc.SwapQuotes(Person.Title) & "',")
                .Append(Person.NationalityId & "," & Person.ResidenceId & "," & Person.UKVisaId & ",")
                .Append(Person.DestinationId & ",")
                .Append("'" & commonFunc.SwapQuotes(Person.JobTitle) & "',")
                .Append("'" & commonFunc.SwapQuotes(Person.Salary) & "',")
                .Append("'" & commonFunc.SwapQuotes(Person.Qualification) & "',")
                .Append("'" & commonFunc.SwapQuotes(Person.PassportNo) & "',")

                If Person.DOB = "#12:00:00 AM#" Then
                    .Append("null,")
                ElseIf IsDate(Person.DOB) Then
                    .Append("'" & commonFunc.FormatDate(Person.DOB) & "',")
                Else
                    .Append("null,")
                End If

                If Person.UKVisaExpiry = "#12:00:00 AM#" Then
                    .Append("null,")
                ElseIf IsDate(Person.UKVisaExpiry) Then
                    .Append("'" & commonFunc.FormatDate(Person.UKVisaExpiry) & "',")
                Else
                    .Append("null,")
                End If

                If Person.PassportExp = "#12:00:00 AM#" Then
                    .Append("null,")
                ElseIf IsDate(Person.PassportExp) Then
                    .Append("'" & commonFunc.FormatDate(Person.PassportExp) & "',")
                Else
                    .Append("null,")
                End If

                .Append(Person.CompanyId & "," & Person.CompanyRoleId & "," & Person.AccountManagerId & ")")

            End With
            Dim DB As New Global_Visas_Data_Layer.DataAccess()
            Dim sReturn As Long = DB.ExecuteSQL(SQL.ToString, True)

            'If sReturn = 0 Then
            '    Return sReturn
            'End If
            'Return Person_Id
            'Return DB.GetIdentity
            Return sReturn

        ElseIf Person.PersonId > 0 Then
            'Edit mode
            Dim Changes As New StringBuilder

            With SQL
                .Append("update d_person set person_fname = '" & commonFunc.SwapQuotes(Person.Firstname.Trim) & "',")
                .Append("person_lname = '" & commonFunc.SwapQuotes(Person.Surname.Trim) & "',")
                .Append("person_title = '" & commonFunc.SwapQuotes(Person.Title.Trim) & "',")
                If Person.DOB = "#12:00:00 AM#" Then
                    .Append("person_dob = " & "null" & ",")
                Else
                    .Append("person_dob = '" & commonFunc.FormatDate(Person.DOB) & "',")
                End If
                .Append("person_jobtitle = '" & commonFunc.SwapQuotes(Person.JobTitle) & "',")
                .Append("person_qualification = '" & commonFunc.SwapQuotes(Person.Qualification) & "',")
                .Append("person_salary = '" & commonFunc.SwapQuotes(Person.Salary) & "',")
                .Append("person_companyroleid = " & Person.CompanyRoleId & ",")
                .Append("person_destinationid = " & Person.DestinationId & ",")
                .Append("person_natid = " & Person.NationalityId & ",")
                .Append("person_residentid = " & Person.ResidenceId & ",")
                .Append("person_ukvisaid = " & Person.UKVisaId & ",")
                If Person.UKVisaExpiry = "#12:00:00 AM#" Then
                    .Append("person_expirydt = " & "null" & ",")
                Else
                    .Append("person_expirydt = '" & commonFunc.FormatDate(Person.UKVisaExpiry) & "',")
                End If
                .Append("person_passportno = '" & commonFunc.SwapQuotes(Person.PassportNo) & "',")
                If Person.PassportExp = "#12:00:00 AM#" Then
                    .Append("person_passportdtexpiry = " & "null" & ",")
                Else
                    .Append("person_passportdtexpiry = '" & commonFunc.FormatDate(Person.PassportExp) & "',")
                End If

                .Append("person_accountmanager = " & Person.AccountManagerId & ",")
                .Append("person_skilledoccid = " & Person.SkilledOccupationId & ",")
                .Append("person_enablelogin = " & Person.EnableLogin)
                .Append(" where person_id = " & Person.PersonId)
            End With

            With Changes

                .Append("Details changed for " & OldPerson.Firstname & " " & OldPerson.Surname & vbCrLf & "----------------------------------" & vbCrLf)

                If Person.Surname <> OldPerson.Surname Then
                    .Append("Surname changed from " & OldPerson.Surname & " to " & Person.Surname & vbCrLf)
                End If

                If Person.Firstname <> OldPerson.Firstname Then
                    .Append("First name changed from " & OldPerson.Firstname & " to " & Person.Firstname & vbCrLf)
                End If

                If Person.Title <> OldPerson.Title Then
                    .Append("Title changed from " & OldPerson.Title & " to " & Person.Title & vbCrLf)
                End If

                If Person.DOB <> OldPerson.DOB Then
                    .Append("DoB changed from " & OldPerson.DOB & " to " & Person.DOB & vbCrLf)
                End If

                If Person.AccountManagerId <> OldPerson.AccountManagerId Then
                    .Append("Account manager changed from " & CommonDataFunctions.GetConsultantName(OldPerson.AccountManagerId) & " to " & CommonDataFunctions.GetConsultantName(Person.AccountManagerId) & vbCrLf)
                End If

                If Person.UKVisaExpiry <> OldPerson.UKVisaExpiry Then
                    .Append("UK Visa expiry date changed from " & OldPerson.UKVisaExpiry & " to " & Person.UKVisaExpiry & vbCrLf)
                End If

                If Person.Salary <> OldPerson.Salary Then
                    .Append("Salary changed from " & OldPerson.Salary & " to " & Person.Salary & vbCrLf)
                End If

                If Person.SkilledOccupationId <> OldPerson.SkilledOccupationId Then
                    .Append("Skilled occupation changed from " & OldPerson.SkilledOccupationId & " to " & Person.SkilledOccupationId & vbCrLf)
                End If

                If Person.NationalityId <> OldPerson.NationalityId Then
                    .Append("Nationality changed from " & OldPerson.NationalityName & " to " & Person.NationalityName & vbCrLf)
                End If

                If Person.ResidenceId <> OldPerson.ResidenceId Then
                    .Append("Residence country changed from " & OldPerson.ResidenceName & " to " & Person.ResidenceName & vbCrLf)
                End If

                If Person.DestinationId <> OldPerson.DestinationId Then
                    .Append("Destination country changed from " & OldPerson.DestinationName & " to " & Person.DestinationName & vbCrLf)
                End If

                If Person.UKVisaId <> OldPerson.UKVisaId Then
                    .Append("UK Visa status changed from " & OldPerson.UKVisaName & " to " & Person.UKVisaName & vbCrLf)
                End If

                If Person.JobTitle <> OldPerson.JobTitle Then
                    .Append("Job title changed from " & OldPerson.JobTitle & " to " & Person.JobTitle & vbCrLf)
                End If

                If Person.PassportNo <> OldPerson.PassportNo Then
                    .Append("Passport number changed from " & OldPerson.PassportNo & " to " & Person.PassportNo & vbCrLf)
                End If

                If Person.PassportExp <> OldPerson.PassportExp Then
                    .Append("Passport expiry date changed from " & OldPerson.PassportExp & " to " & Person.PassportExp & vbCrLf)
                End If

                If Person.Qualification <> OldPerson.Qualification Then
                    .Append("Qualification changed from " & OldPerson.Qualification & " to " & Person.Qualification & vbCrLf)
                End If

                If Person.CompanyRoleId <> Person.CompanyRoleId Then
                    .Append("Company role changed from " & OldPerson.CompanyRoleId & " to " & Person.CompanyRoleId & vbCrLf)
                End If
                If Person.EnableLogin <> Person.EnableLogin Then
                    .Append("Login Enabling changed from " & OldPerson.EnableLogin & " to " & Person.EnableLogin & vbCrLf)
                End If

                If Person.WhereHeard <> OldPerson.WhereHeard Then
                    .Append("Where heard changed from " & OldPerson.WhereHeard & " to " & Person.WhereHeard & vbCrLf)
                End If

            End With
            Dim DB As New Global_Visas_Data_Layer.DataAccess()
            If DB.ExecuteSQL(SQL.ToString) Then

                ' catch people who do not have a Where Hear value
                If IsNothing(OldPerson.WhereHeard) Then
                    AddAdvertising(Person.WhereHeard, GlobalCRM.CurrentConName, GlobalCRM.CurrentCandidateID)
                ElseIf Person.WhereHeard <> OldPerson.WhereHeard Then
                    UpdateAdvertising(GlobalCRM.CurrentCandidateID, Person.WhereHeard)
                End If

                'If Person.WhereHeard <> OldPerson.WhereHeard Then
                '    UpdateAdvertising(GlobalCRM.CurrentCandidateID, Person.WhereHeard)
                'End If
                Dim tasksEvents As New TasksEvents()
                tasksEvents.GblFunc_Add_Event("Details Changed", Changes.ToString, tasksEvents.EventStatus.SingleEvent, GlobalCRM.CurrentCandidateID, CommonDataFunctions.EventTypes.EventPerson)

                Return Person.PersonId
            End If

        End If

    End Function

    'Public Shared Function AddEmailAddress(ByVal EmailAddress As String, ByVal EmailName As String, ByVal ConName As String, ByVal PersonId As Long) As Long

    '    Dim SQL As New StringBuilder        Dim commonFunc As New CommonFuncs()

    '    With SQL
    '        .Append("Insert into D_EMailAddress (EMailAddress_Address, EMailAddress_Name, EMailAddress_Type, EMailAddress_EnteredBy, EMailAddress_DTEntered, EMailAddress_PersonID)")
    '        .Append(" Values ('" & commonFunc.SwapQuotes(EmailAddress.Trim) & "','" & commonFunc.SwapQuotes(EmailName.Trim) & "',1,'")
    '        .Append(commonFunc.SwapQuotes(ConName.Trim) & "','" & commonFunc.FormatDate(Now, True) & "'," & PersonId & ")")
    '    End With

    '    If DB.ExecuteSQL(SQL.ToString) Then
    '        'Email ID
    '        Return DB.GetIdentity
    '    End If

    'End Function

    Public Shared Function DeletePhoneNumber(ByVal PhoneId As Long, Optional ByVal PersonId As Long = 0, Optional ByVal PhoneName As String = "", Optional ByVal PhoneNumber As String = "") As Boolean

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()

        With SQL
            .Append("delete from d_phonenumber where phonenumber_id = " & PhoneId)
        End With
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        DeletePhoneNumber = DB.ExecuteSQL(SQL.ToString)

        If (PhoneName <> "" Or PhoneNumber <> "") And PersonId > 0 Then

            Dim Msg As New StringBuilder

            Msg.Append("Phone number deleted" & vbCrLf & vbCrLf & PhoneName.Trim & vbCrLf & PhoneNumber.Trim)
            Dim taskEvents As New TasksEvents()
            taskEvents.GblFunc_Add_Event("Details Changed", Msg.ToString, TasksEvents.EventStatus.SingleEvent, PersonId, TasksEvents.LinkTypes.Person)
        End If

    End Function

    Public Shared Function DeleteEmailAddress(ByVal EmailId As Long, Optional ByVal PersonId As Long = 0, Optional ByVal EmailName As String = "", Optional ByVal EmailAddress As String = "") As Boolean

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()

        SQL.Append("DELETE FROM D_EMailAddress WHERE EMailAddress_ID = " & EmailId)
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        DeleteEmailAddress = DB.ExecuteSQL(SQL.ToString)

        If (EmailName <> "" Or EmailAddress <> "") And EmailId > 0 Then

            Dim Msg As New StringBuilder

            Msg.Append("Email address deleted" & vbCrLf & vbCrLf & EmailName.Trim & vbCrLf & EmailAddress.Trim)
            Dim taskEvents As New TasksEvents()
            taskEvents.GblFunc_Add_Event("Details Changed", Msg.ToString, TasksEvents.EventStatus.SingleEvent, PersonId, TasksEvents.LinkTypes.Person)
        End If

    End Function

    Public Shared Function AddPhoneNumber(ByVal PhoneNumber As String, ByVal PhoneName As String, ByVal ConName As String, ByVal PersonId As Long, ByVal PhoneType As PhoneTypes, Optional ByVal PhoneNumberCompanyId As Long = 0, Optional ByVal PhoneId As Long = 0, Optional ByVal OldPhoneName As String = "", Optional ByVal OldPhoneNumber As String = "") As Long

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()
        Dim Msg As New StringBuilder
        Dim NewPhoneId As Long
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        If PhoneId = 0 Then

            'New phone number

            'See if person has any other numbers, if not set it as the main number

            SQL.Append("select count(*) from d_phonenumber where phonenumber_personid = " & PersonId)

            If DB.GetSQLScalar(SQL.ToString) = 0 Then
                PhoneType = PhoneTypes.PhoneMain
            End If

            SQL = New StringBuilder

            With SQL
                .Append("Insert into D_PhoneNumber (Phonenumber_number, Phonenumber_Name, Phonenumber_Type, PhoneNumber_CompanyID, Phonenumber_EnteredBy, Phonenumber_DTEntered, Phonenumber_PersonID)")
                .Append(" Values('" & commonFunc.SwapQuotes(PhoneNumber.Trim) & "','" & commonFunc.SwapQuotes(PhoneName.Trim) & "'," & PhoneType & "," & PhoneNumberCompanyId & ",")
                .Append("'" & commonFunc.SwapQuotes(ConName) & "','" & commonFunc.FormatDate(Now, True) & "'," & PersonId & ")")
            End With

            Msg.Append("New phone number added." & vbCrLf & vbCrLf & PhoneName.Trim & vbCrLf & PhoneNumber.Trim)

        Else
            'Edit existing number
            With SQL
                .Append("update d_phonenumber set Phonenumber_number = '" & commonFunc.SwapQuotes(PhoneNumber.Trim) & "',")
                .Append("phonenumber_name = '" & PhoneName.Trim & "' ")
                .Append("where phonenumber_id = " & PhoneId)
            End With

            With Msg
                .Append("Phone number changed." & vbCrLf & vbCrLf)

                If PhoneName <> OldPhoneName Then
                    .Append("'" & commonFunc.SwapQuotes(OldPhoneName.Trim) & "' to '" & commonFunc.SwapQuotes(PhoneName.Trim) & "'" & vbCrLf)
                End If

                If PhoneNumber <> OldPhoneNumber Then
                    .Append("'" & commonFunc.SwapQuotes(OldPhoneNumber.Trim) & "' to '" & commonFunc.SwapQuotes(PhoneNumber.Trim) & "'")
                End If
            End With

        End If

        If PhoneId = 0 Then
            NewPhoneId = DB.ExecuteSQL(SQL.ToString, True)

        Else

            If DB.ExecuteSQL(SQL.ToString) Then
                NewPhoneId = PhoneId
            End If
        End If

        If NewPhoneId > 0 Then
            'Return Phone ID
            Dim taskEvents As New TasksEvents()
            taskEvents.GblFunc_Add_Event("Details Changed", Msg.ToString, TasksEvents.EventStatus.SingleEvent, PersonId, TasksEvents.LinkTypes.Person)
            Return NewPhoneId
        End If

    End Function

    Public Shared Function AddDependant(ByVal ParentPersonId As Long, ByVal PersonId As Long, ByVal ParentDescription As String, ByVal ChildDescription As String, ByVal ConName As String, ByVal PublicRelationship As Boolean) As Long
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()
        Try

        
            With SQL
                .Append("Insert into D_Dependants (Dependant_ParentID, Dependant_ChildID, Dependant_Parentdesc, Dependant_Childdesc, Dependant_Enteredby, Dependant_Public)")
                .Append(" Values(" & ParentPersonId & ", " & PersonId & ", '" & commonFunc.SwapQuotes(ParentDescription.Trim) & "',")
                .Append("'" & commonFunc.SwapQuotes(ChildDescription.Trim) & "','" & ConName & "',")
                .Append(IIf(PublicRelationship, 1, 0) & ")")
            End With

            DB.ExecuteSQL(SQL.ToString, True)
           
        Catch ex As Exception
            Return 0
        Finally
            SQL = Nothing
            DB = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function SetMainOrFaxPhoneNumber(ByVal PhoneId As Long, ByVal ChangeToPhoneType As PhoneTypes, ByVal PersonId As Long, ByVal PhoneName As String, ByVal PhoneNumber As String) As Boolean

        Dim SQL As StringBuilder
        Dim Msg As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Select Case ChangeToPhoneType
            Case PhoneTypes.PhoneBoth
                'Phone is already set as fax, so set to Both

                'Set any existing main number to zero
                SQL = New StringBuilder

                SQL.Append("Update D_PhoneNumber set Phonenumber_Type = 0 where Phonenumber_PersonID = " & PersonId & " AND Phonenumber_Type in (" & PhoneTypes.PhoneMain & "," & PhoneTypes.PhoneFax & ")")

                If Not DB.ExecuteSQL(SQL.ToString) Then
                    Exit Function
                End If

                SQL = New StringBuilder

                'Set this number to be both (3)
                SQL.Append("Update D_PhoneNumber set Phonenumber_Type = " & PhoneTypes.PhoneBoth & " where Phonenumber_ID = " & PhoneId)

                If Not DB.ExecuteSQL(SQL.ToString) Then
                    Exit Function
                End If

                Msg.Append("Phone number '" & PhoneName.Trim & "' (" & PhoneNumber.Trim & ") set as both main and fax number.")


            Case PhoneTypes.PhoneMain, PhoneTypes.PhoneFax
                'Is nothing so set to Main

                'Set existing numbers to 0 or 2, depending on what they are now. (using case update saves doing two updates)

                SQL = New StringBuilder

                With SQL
                    .Append("UPDATE D_PhoneNumber ")
                    .Append(" SET Phonenumber_type = ")
                    If ChangeToPhoneType = PhoneTypes.PhoneMain Then
                        .Append(" CASE phonenumber_type WHEN 3 THEN 2 WHEN 1 THEN 0 END")
                        .Append(" WHERE Phonenumber_PersonID = " & PersonId)
                        .Append(" AND Phonenumber_type in (1,3)")
                    ElseIf ChangeToPhoneType = PhoneTypes.PhoneFax Then
                        .Append(" CASE phonenumber_type WHEN 3 THEN 1 WHEN 2 THEN 0 END")
                        .Append(" WHERE Phonenumber_PersonID = " & PersonId)
                        .Append(" AND Phonenumber_type in (2,3)")
                    End If
                End With

                If Not DB.ExecuteSQL(SQL.ToString) Then
                    Exit Function
                End If

                SQL = New StringBuilder

                'Set this number to main / fax
                SQL.Append("Update D_PhoneNumber set Phonenumber_Type = " & ChangeToPhoneType & " where Phonenumber_ID = " & PhoneId)

                If Not DB.ExecuteSQL(SQL.ToString) Then
                    Exit Function
                End If

                If ChangeToPhoneType = PhoneTypes.PhoneMain Then
                    Msg.Append("Phone number '" & PhoneName.Trim & "' (" & PhoneNumber.Trim & ") set as main number.")
                ElseIf ChangeToPhoneType = PhoneTypes.PhoneFax Then
                    Msg.Append("Phone number '" & PhoneName.Trim & "' (" & PhoneNumber.Trim & ") set as fax number.")
                End If

        End Select
        Dim tasksEvents As New TasksEvents()
        tasksEvents.GblFunc_Add_Event("Details Changed", Msg.ToString, tasksEvents.EventStatus.SingleEvent, PersonId, tasksEvents.LinkTypes.Person)

        Return True


    End Function

    Public Shared Function AddEmailAddress(ByVal EmailTitle As String, ByVal EmailAddress As String, ByVal PersonId As Long, ByVal MainEmail As Boolean, ByVal ConName As String, Optional ByVal EmailId As Long = 0, Optional ByVal OldEmailTitle As String = "", Optional ByVal OldEmailAddress As String = "") As Long

        Dim SQL As StringBuilder
        Dim Msg As New StringBuilder
        Dim NewEmailId As Long
        Dim commonFunc As New CommonFuncs()
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Try

        
            If EmailId = 0 Then
                'Adding a new address

                'Check if this is the first adddress, if so it is automaticaly the Main address

                SQL = New StringBuilder

                SQL.Append("select count(*) from d_emailaddress where emailaddress_personid = " & PersonId)

                If DB.GetSQLScalar(SQL.ToString) = 0 Then
                    MainEmail = True
                End If

                SQL = New StringBuilder

                With SQL
                    .Append("Insert into D_EMailAddress (EMailaddress_Name, EMailAddress_DTEntered, EMailAddress_Enteredby, EMailAddress_Address, EMailAddress_PersonID, EMailAddress_Type)")
                    .Append(" Values('" & commonFunc.SwapQuotes(EmailTitle.Trim) & "', '" & commonFunc.FormatDate(Now, True) & "',")
                    .Append("'" & commonFunc.SwapQuotes(ConName) & "', '" & commonFunc.SwapQuotes(EmailAddress) & "', " & PersonId & "," & IIf(MainEmail, 1, 0) & ")")
                End With

                Msg.Append("E-Mail address added" & vbCrLf & vbCrLf & EmailTitle.Trim & vbCrLf & EmailAddress.Trim)
            Else

                'Updating existing email address
                SQL = New StringBuilder

                With SQL
                    .Append("Update D_EmailAddress set EMailAddress_Name = '" & EmailTitle.Trim & "',")
                    .Append("EMailaddress_Address = '" & commonFunc.SwapQuotes(EmailAddress.Trim) & "'")
                    .Append("where EMailaddress_ID = " & EmailId)
                End With

                With Msg
                    .Append("E-Mail address changed. " & vbCrLf & vbCrLf)

                    If EmailTitle <> OldEmailTitle Then
                        .Append(OldEmailTitle & " to " & EmailTitle & vbCrLf)
                    End If

                    If EmailAddress <> OldEmailAddress Then
                        .Append(OldEmailAddress & " to " & EmailAddress)
                    End If
                End With

            End If

            If EmailId > 0 Then
                If DB.ExecuteSQL(SQL.ToString) Then
                    NewEmailId = EmailId
                End If
            ElseIf EmailId = 0 Then
                NewEmailId = DB.ExecuteSQL(SQL.ToString, True)

            End If


            If NewEmailId > 0 Then

                Dim taskEvents As New TasksEvents()
                taskEvents.GblFunc_Add_Event("Details Changed", Msg.ToString, TasksEvents.EventStatus.SingleEvent, PersonId, TasksEvents.LinkTypes.Person)
                Return NewEmailId
            Else
                'MsgBox("The address has not been added/updated. Please contact IT", MsgBoxStyle.Critical, "IT")
                Return 0
            End If
        Catch ex As Exception
            Dim incidentno As Long
            incidentno = CommonFuncs.ErrorHandler(ex, "Addemailaddress")
            If incidentno > 0 Then
                MsgBox("The incident has been created and sent to IT." & vbCrLf & vbCrLf & "Please quote the incident number : " & incidentno & " to It for future correspondences", MsgBoxStyle.Information, "IT")
            Else
                MsgBox("Please contact IT directly. The message was not created.", MsgBoxStyle.Critical, "IT")
            End If
        End Try
    End Function

    Public Shared Function SetMainEmailAddress(ByVal EmailId As Long, ByVal PersonId As Long, ByVal EmailName As String, ByVal EmailAddress As String) As Boolean
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As StringBuilder
        Dim commonFunc As New CommonFuncs()
        Try
            SQL = New StringBuilder
            With SQL
                .Append("UPDATE D_EMailaddress SET EMailaddress_Type = ")
                .Append(" CASE EmailAddress_ID WHEN " & EmailId & " THEN 1 ELSE 0 END ")
                .Append(" WHERE Emailaddress_PersonID = " & PersonId)
            End With

            If DB.ExecuteSQL(SQL.ToString) Then

                Dim Msg As New StringBuilder

                Msg.Append("Email address " & EmailName.Trim & " (" & EmailAddress.Trim & ") set as main address")
                Dim taskEvents As New TasksEvents()
                taskEvents.GblFunc_Add_Event("Details Changed", Msg.ToString, TasksEvents.EventStatus.SingleEvent, PersonId, TasksEvents.LinkTypes.Person)

                Return True
            End If
        Catch ex As Exception


        Finally
            DB = Nothing
            SQL = Nothing
            commonFunc = Nothing
        End Try

    End Function

    Public Shared Function AddAddress(ByVal Address As String, ByVal CountyOrRegion As String, ByVal Country As String, ByVal Postcode As String, ByVal ConName As String, ByVal AddressType As AddressTypes, ByVal AddressName As String, Optional ByVal LinkId As Long = 0, Optional ByVal AddressLinkId As Long = 0, Optional ByVal AddressId As Long = 0, Optional ByVal OldAddressName As String = "", Optional ByVal OldAddress As String = "", Optional ByVal OldPostcode As String = "", Optional ByVal OldCountry As String = "", Optional ByVal OldRegion As String = "") As Long

        'Add the address to the main address table

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()
        'Dim AddressId As Long
        Dim LinkCol As String
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        If AddressLinkId = 0 Then

            'New address record
            If (String.IsNullOrEmpty(ConName)) Then
                ConName = String.Empty
            End If
            With Sql
                .Append("INSERT INTO D_Address ( Address_Text, Address_EnteredBy, Address_DTEntered, Address_Postcode, Address_Region, Address_Country )")
                .Append(" VALUES ( '" & commonFunc.SwapQuotes(Address.Trim) & "', '" & commonFunc.SwapQuotes(ConName.Trim) & "', '" & commonFunc.FormatDate(Now, True) & "', '" & commonFunc.SwapQuotes(Postcode.Trim) & "',")
                .Append("'" & commonFunc.SwapQuotes(CountyOrRegion.Trim) & "',")
                .Append("'" & Country.Trim & "')")
            End With

            'If Not DB.ExecuteSQL(Sql.ToString) Then
            '            Exit Function
            'End If
            AddressId = DB.ExecuteSQL(SQL.ToString, True)
            'AddressId = DB.GetIdentity
            If AddressId = 0 Then
                Exit Function
            End If

            'Add the link to the company/person

            If AddressType = AddressTypes.Company Then
                LinkCol = "AddressLink_CompanyID"
            Else
                LinkCol = "AddressLink_PersonID"
            End If

            SQL = New StringBuilder

            With SQL
                .Append("INSERT INTO D_AddressLink ( AddressLink_AddressID, AddressLink_EnteredBy, AddressLink_DTEntered, ")
                .Append(LinkCol & ", AddressLink_Name, AddressLink_Owner )")
                .Append(" VALUES (" & AddressId & ", '" & commonFunc.SwapQuotes(ConName.Trim) & "', '" & commonFunc.FormatDate(Now, True) & "',")
                .Append(LinkId & ",'" & commonFunc.SwapQuotes(AddressName.Trim) & "',1)")
            End With

            Return DB.ExecuteSQL(SQL.ToString, True)

            'End If

        Else
            'Update existing record
            Dim NameChanged, Dirty As Boolean
            Dim Msg As New StringBuilder
            Dim OldString As String

            If AddressName <> OldAddressName Then
                NameChanged = True
                Msg.Append("Address name changed from " & OldAddressName.Trim & " to " & AddressName.Trim & Chr(13) & Chr(10))
            End If

            SQL.Append("Update D_Address Set ")
            If StrComp(Address, OldAddress) <> 0 Then
                Dirty = True

                If OldAddress.Trim = String.Empty Then
                    OldString = "Blank"
                Else
                    OldString = vbCrLf & "----------" & vbCrLf & OldAddress.Trim & vbCrLf & "----------" & vbCrLf
                End If

                SQL.Append(" Address_text = '" & commonFunc.SwapQuotes(Address.Trim) & "', ")
                Msg.Append("Address changed from " & OldString & " to " & vbCrLf & "----------" & vbCrLf & commonFunc.SwapQuotes(Address.Trim) & vbCrLf & "----------" & vbCrLf)
            End If


            If Postcode.Trim <> OldPostcode.Trim Then

                If Postcode.Trim = String.Empty Then
                    OldString = "blank"
                Else
                    OldString = OldPostcode.Trim
                End If

                Dirty = True
                SQL.Append(" Address_postcode = '" & commonFunc.SwapQuotes(Postcode.Trim) & "', ")
                Msg.Append("Postcode changed from " & OldString & " to " & commonFunc.SwapQuotes(Postcode.Trim) & vbCrLf)
            End If

            If Country.Trim <> OldCountry.Trim Then

                If OldCountry.Trim = String.Empty Then
                    OldString = "blank"
                Else
                    OldString = OldCountry.Trim
                End If

                Dirty = True
                SQL.Append(" Address_country = '" & commonFunc.SwapQuotes(Country.Trim) & "', ")
                Msg.Append("Country changed from " & OldString & " to " & commonFunc.SwapQuotes(Country.Trim) & vbCrLf)
            End If

            If CountyOrRegion.Trim <> OldRegion Then
                If OldRegion.Trim = String.Empty Then
                    OldString = "blank"
                Else
                    OldString = OldRegion.Trim
                End If

                Dirty = True

                SQL.Append(" Address_Region = '" & commonFunc.SwapQuotes(CountyOrRegion.Trim) & "', ")
                Msg.Append("County changed from " & OldString & " to " & commonFunc.SwapQuotes(CountyOrRegion.Trim) & vbCrLf)
            End If


            If Not Dirty And Not NameChanged Then
                'Nothing changed
                Return AddressLinkId
                Exit Function
            End If

            If Dirty Then
                SQL.Remove(SQL.Length - 2, 2)
                SQL.Append(" Where Address_ID = " & AddressId)
                If Not DB.ExecuteSQL(SQL.ToString) Then
                    Exit Function
                End If
            End If

            If NameChanged Then
                SQL = New StringBuilder
                With SQL
                    .Append("Update D_AddressLink set Addresslink_Name = '" & commonFunc.SwapQuotes(AddressName.Trim) & "' ")
                    .Append("where AddressLink_ID = " & AddressLinkId)
                End With

                If Not DB.ExecuteSQL(SQL.ToString) Then
                    Exit Function
                End If
            End If
            Dim taskEvents As New TasksEvents()
            If AddressType = AddressTypes.Company Then
                Msg.Append("Company address details, " & OldAddressName.Trim & ", changed as below " & vbCrLf & "-------------------" & vbCrLf & Msg.ToString)
                taskEvents.GblFunc_Add_Event("Details Changed", Msg.ToString, TasksEvents.EventStatus.SingleEvent, GlobalCRM.CurrentCompanyID, TasksEvents.LinkTypes.Company)
            ElseIf AddressType = AddressTypes.Person Then
                Msg.Append("Personal address details for " & GlobalCRM.CurrentCandidateName.Trim & ", " & OldAddressName.Trim & ", changed as below " & vbCrLf & "-------------------" & vbCrLf & Msg.ToString)
                taskEvents.GblFunc_Add_Event("Details Changed", Msg.ToString, TasksEvents.EventStatus.SingleEvent, GlobalCRM.CurrentCandidateID, TasksEvents.LinkTypes.Person)
            End If

            Return AddressLinkId

        End If

    End Function

    Public Shared Function GetAddressForContract(ByVal personId As Long) As String
        Dim address As String = String.Empty
        Dim myData As System.Data.DataTable
        myData = PrivateIndividual.GetPersonAddressSQL(GlobalCRM.CurrentCandidateID)
        If (myData.Rows.Count > 0) Then
            Dim addresslikdId As Long = Convert.ToDouble(myData.Rows(0).Item("AddressLink_ID"))
            address = CommonDataFunctions.GetAddressText(addresslikdId)
        End If
        Return address
    End Function

    Public Shared Function GetAddress(ByVal AddressLinkId As Long) As AddressInfo

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()
        Dim myData As DataTable
        Dim myRow As DataRow
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        GetAddress = Nothing

        With SQL
            .Append("select a.address_id, a.address_text,a.address_postcode,a.address_region,a.address_country,lnk.addresslink_name ")
            .Append("from d_addresslink lnk, d_address a ")
            .Append("where lnk.addresslink_addressid = a.address_id ")
            .Append("and lnk.addresslink_id = " & AddressLinkId)
        End With

        myData = DB.GetSQLDataTable(SQL.ToString)

        If Not IsNothing(myData) Then
            If myData.Rows.Count > 0 Then
                myRow = myData.Rows(0)

                Dim myAddress As New AddressInfo

                With myAddress
                    .AddressId = myRow.Item("address_id")
                    .AddressText = myRow.Item("address_text")
                    .Postcode = myRow.Item("address_postcode")
                    .Region = myRow.Item("address_region")
                    .CountryName = myRow.Item("address_country")
                    .AddressName = myRow.Item("addresslink_name")
                End With

                Return myAddress

            End If
        End If

    End Function

    Public Shared Function GetAddressIdFromAddressLink(ByVal AddressLinkId As Long) As Long
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLScalar("select addresslink_addressid from d_addresslink where addresslink_id = " & AddressLinkId)

    End Function

    Public Shared Function DeleteAddress(ByVal AddressLinkId As Long, ByVal AddressType As AddressTypes, Optional ByVal PersonName As String = "") As Boolean

        Dim AddressId As Long = GetAddressIdFromAddressLink(AddressLinkId)
        Dim OldAddress As AddressInfo = GetAddress(AddressLinkId)
        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()
        Dim Msg As New StringBuilder

        SQL.Append("Select addresslink_type from D_Addresslink where Addresslink_AddressID = " & AddressId)
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        If CType(DB.GetSQLScalar(SQL.ToString), AddressLinkTypes) <> AddressLinkTypes.NotSet Then
            MsgBox("This address is in use as either a correspondence or invoice address, it cannot be deleted until these functions are moved to a different address", MsgBoxStyle.Exclamation)
            Exit Function
        End If

        If MsgBox("Are you sure you want to delete this address?", vbYesNo + vbExclamation, "Confirm delete") <> MsgBoxResult.Yes Then
            Exit Function
        End If

        With Msg

            If AddressType = AddressTypes.Company Then
                .Append("Company Address deleted (removed details below)")
            Else
                .Append("Address for " & PersonName.Trim & " deleted (removed details below)")
            End If

            .Append(vbCrLf & "Name: " & OldAddress.AddressName.Trim)
            .Append(OldAddress.AddressText.Trim)

            If OldAddress.Region.Trim <> "" Then
                .Append(vbCrLf & OldAddress.Region.Trim)
            End If

            If OldAddress.Postcode.Trim <> "" Then
                .Append(vbCrLf & OldAddress.Postcode.Trim)
            End If

            If OldAddress.CountryName.Trim <> "" Then
                .Append(vbCrLf & OldAddress.CountryName.Trim)
            End If

        End With

        SQL = New StringBuilder
        SQL.Append("Delete from D_address where address_ID = " & AddressId)

        If Not DB.ExecuteSQL(SQL.ToString) Then
            Exit Function
        End If

        SQL = New StringBuilder
        SQL.Append("Delete from D_addresslink where addresslink_addressID = " & AddressLinkId)

        If Not DB.ExecuteSQL(SQL.ToString) Then
            Exit Function
        End If
        Dim taskEvents As New TasksEvents()
        If AddressType = AddressTypes.Company Then
            taskEvents.GblFunc_Add_Event("Details Changed", Msg.ToString, TasksEvents.EventStatus.SingleEvent, GlobalCRM.CurrentCompanyID, TasksEvents.LinkTypes.Company)
        ElseIf AddressType = AddressTypes.Person Then
            taskEvents.GblFunc_Add_Event("Details Changed", Msg.ToString, TasksEvents.EventStatus.SingleEvent, GlobalCRM.CurrentCandidateID, TasksEvents.LinkTypes.Person)
        End If

        Return True

    End Function

    Public Shared Function SetCorrespOrInvAddress(ByVal AddressLinkId As Long, ByVal PersonId As Long, ByVal AddressName As String, ByVal ChangeTo As AddressLinkTypes) As Boolean

        'Flags an address as being used for correspondence and / or invoices

        Dim Msg As New StringBuilder
        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        'Set any existing 'both' value back to 'Inv' and main value to nothing
        With SQL
            Select Case ChangeTo
                Case AddressLinkTypes.Both
                    .Append("Update D_AddressLink SET AddressLink_Type = case addresslink_type when 2 then 0 when 1 then 0 when 3 then 0 end ")
                    .Append("WHERE AddressLink_PersonID = " & PersonId & " AND AddressLink_Type in (1,2)")
                Case AddressLinkTypes.Correspondence
                    .Append("Update D_AddressLink SET AddressLink_Type = case addresslink_type when 1 then 0 when 3 then 2 end ")
                    .Append("WHERE AddressLink_PersonID = " & PersonId & " AND AddressLink_Type in (1,3)")
                Case AddressLinkTypes.Invoice
                    .Append("Update D_AddressLink SET AddressLink_Type = case addresslink_type when 2 then 0 when 3 then 1 end ")
                    .Append("WHERE AddressLink_PersonID = " & PersonId & " AND AddressLink_Type in (2,3)")
            End Select
        End With

        If Not DB.ExecuteSQL(SQL.ToString) Then
            Exit Function
        End If

        'Set selected address to Corr, Inv or Both
        If ChangeTo = AddressLinkTypes.Correspondence Then Msg.Append(AddressName.Trim & " set as correspondence address")
        If ChangeTo = AddressLinkTypes.Invoice Then Msg.Append(AddressName.Trim & " set as invoice address")
        If ChangeTo = AddressLinkTypes.Both Then Msg.Append(AddressName.Trim & " set as both correspondence and invoice address")

        SQL = New StringBuilder
        SQL.Append("Update D_AddressLink SET AddressLink_Type = " & ChangeTo & " WHERE AddressLink_ID = " & AddressLinkId)

        If Not DB.ExecuteSQL(SQL.ToString) Then
            Exit Function
        End If
        Dim taskEvents As New TasksEvents()
        taskEvents.GblFunc_Add_Event("Details Changed", Msg.ToString, TasksEvents.EventStatus.SingleEvent, PersonId, TasksEvents.LinkTypes.Person)

        Return True

    End Function

    Public Shared Function SetAddressAsCompany(ByVal AddressLinkId As Long, ByVal ConName As String, ByVal CompanyId As Long, ByVal AddressName As String) As Boolean

        Dim AddressId As Long = GetAddressIdFromAddressLink(AddressLinkId)
        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        If AddressId = 0 Then
            Exit Function
        End If

        With SQL
            .Append("INSERT INTO D_AddressLink ( AddressLink_AddressID, AddressLink_EnteredBy, AddressLink_DTEntered, AddressLink_CompanyID, AddressLink_Name, AddressLink_Owner )")
            .Append(" VALUES (" & AddressId & ", '" & commonFunc.SwapQuotes(ConName.Trim) & "', getdate(), " & CompanyId & ",'" & commonFunc.SwapQuotes(AddressName.Trim) & "', 1)")
        End With

        If Not DB.ExecuteSQL(SQL.ToString) Then
            Exit Function
        End If

        SQL = New StringBuilder

        With SQL
            .Append("UPDATE D_AddressLink SET AddressLink_Owner = 0")
            .Append(" WHERE AddressLink_ID = " & AddressLinkId)
        End With

        If Not DB.ExecuteSQL(SQL.ToString) Then
            Exit Function
        End If

        Return True


    End Function

    Public Shared Function GetPerson(ByVal PersonID As Long) As PersonInfo

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()
        Dim myData As DataTable

        GetPerson = Nothing

        With SQL

            '.Append("select p.person_id,p.person_jobtitle,p.person_qualification,p.person_salary,p.person_dob,p.person_passportno,p.person_passportdtexpiry,")
            '.Append("p.person_destinationid,p.person_fname,p.person_lname,p.person_title,p.person_accountmanager,c1.country_name as destination,p.person_natid,")
            '.Append("c2.country_nationality,p.person_residentid, c3.country_name as resident,")
            '.Append("p.person_ukvisaid,p.person_expirydt as ukvisaexpiry,ukv.ukvisa_name,p.person_companyroleid, ")
            '.Append("p.person_webusername,p.person_webpassword,p.person_webaccess,p.person_skilledoccid ")
            '.Append("from d_person p, d_person p2 ")
            '.Append("left outer join l_ukvisa ukv on p2.person_ukvisaid = ukv.ukvisa_id ")
            '.Append("left outer join l_country c1 on p2.person_destinationid = c1.country_id ")
            '.Append("left outer join l_country c2 on p2.person_natid = c2.country_id ")
            '.Append("left outer join l_country c3 on p2.person_residentid = c3.country_id ")
            '.Append("where p.person_id = " & PersonID & " And p.person_id = p2.person_id")


            .Append("select p.person_id,p.person_enablelogin,p.person_jobtitle,p.person_qualification,p.person_salary,p.person_dob,p.person_passportno,")
            .Append("p.person_passportdtexpiry,p.person_destinationid,p.person_fname,p.person_lname,p.person_title,p.person_accountmanager,")
            .Append("c1.country_name as destination,p.person_natid,c2.country_nationality,p.person_residentid, c3.country_name as resident,")
            .Append("p.person_ukvisaid,p.person_expirydt as ukvisaexpiry,ukv.ukvisa_name,p.person_companyroleid, p.person_webusername,")
            .Append("p.person_webpassword, p.person_webaccess, p.person_skilledoccid, sk.skilledocc_title ")
            .Append("from d_person p, d_person p2 left outer join l_ukvisa ukv on p2.person_ukvisaid = ukv.ukvisa_id ")
            .Append("left outer join l_country c1 on p2.person_destinationid = c1.country_id left outer join l_country c2 on p2.person_natid = c2.country_id ")
            .Append("left outer join l_country c3 on p2.person_residentid = c3.country_id, ")
            .Append("d_person p3 left outer join l_skilledocc sk on p3.person_skilledoccid = sk.skilledocc_id ")
            .Append("where p.person_id = " & PersonID & " And p.person_id = p2.person_id ")
            .Append("and p.person_id = p3.person_id")
            Dim DB As New Global_Visas_Data_Layer.DataAccess()
            myData = DB.GetSQLDataTable(.ToString)

            Try

                If Not IsNothing(myData) Then
                    If myData.Rows.Count > 0 Then

                        Dim myPerson As New People.PersonInfo
                        Dim myRow As DataRow = myData.Rows(0)

                        With myPerson
                            .Surname = IIf(IsDBNull(myRow.Item("person_lname")), "", myRow.Item("person_lname"))
                            .Firstname = IIf(IsDBNull(myRow.Item("person_fname")), "", myRow.Item("person_fname"))
                            .Title = IIf(IsDBNull(myRow.Item("person_title")), "", myRow.Item("person_title"))
                            .DestinationId = myRow.Item("person_destinationid")
                            .DestinationName = IIf(IsDBNull(myRow.Item("destination")), "", myRow.Item("destination"))
                            .DOB = IIf(IsDBNull(myRow.Item("person_dob")), Nothing, myRow.Item("person_dob"))
                            .JobTitle = IIf(IsDBNull(myRow.Item("person_jobtitle")), "", myRow.Item("person_jobtitle"))
                            .NationalityId = myRow.Item("person_natid")
                            .NationalityName = IIf(IsDBNull(myRow.Item("country_nationality")), "", myRow.Item("country_nationality"))
                            .PassportNo = IIf(IsDBNull(myRow.Item("person_passportno")), "", myRow.Item("person_passportno"))
                            .PassportExp = IIf(IsDBNull(myRow.Item("person_passportdtexpiry")), Nothing, myRow.Item("person_passportdtexpiry"))
                            .PersonId = myRow.Item("person_id")
                            .Qualification = IIf(IsDBNull(myRow.Item("person_qualification")), "", myRow.Item("person_qualification"))
                            .ResidenceId = myRow.Item("person_residentid")
                            .ResidenceName = IIf(IsDBNull(myRow.Item("resident")), "", myRow.Item("resident"))
                            .Salary = IIf(IsDBNull(myRow.Item("person_salary")), "", myRow.Item("person_salary"))
                            .UKVisaExpiry = IIf(IsDBNull(myRow.Item("ukvisaexpiry")), Nothing, myRow.Item("ukvisaexpiry"))
                            .UKVisaId = myRow.Item("person_ukvisaid")
                            .UKVisaName = IIf(IsDBNull(myRow.Item("ukvisa_name")), "", myRow.Item("ukvisa_name"))
                            .AccountManagerId = myRow.Item("person_accountmanager")
                            .WhereHeard = CommonDataFunctions.GetWhereHeard(PersonID, CommonDataFunctions.AdvertLinkType.Person)
                            .CompanyRoleId = myRow.Item("person_companyroleid")
                            .WebUserName = IIf(IsDBNull(myRow.Item("person_webusername")), "", myRow.Item("person_webusername"))
                            .WebPassword = IIf(IsDBNull(myRow.Item("person_webpassword")), "", myRow.Item("person_webpassword"))
                            .WebAccess = myRow.Item("person_webaccess")
                            .SkilledOccupationId = myRow.Item("person_skilledoccid")
                            .SkillOccupationName = IIf(IsDBNull(myRow.Item("skilledocc_title")), "", myRow.Item("skilledocc_title"))
                            .EnableLogin = myRow.Item("person_enablelogin")
                        End With

                        Return myPerson

                    End If
                End If

            Catch ex As System.Exception
                MsgBox(ex.Message, MsgBoxStyle.Critical)

            Finally
                If Not IsNothing(myData) Then
                    myData.Dispose()
                End If
            End Try

        End With

    End Function

    Public Shared Function UpdateVisaExpiryDate(ByVal PersonId As Long, ByVal VisaExpiry As Date, ByVal EventMessage As String) As Boolean
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim commonFunc As New CommonFuncs()
        If DB.ExecuteSQL("Update D_Person set Person_ExpiryDT = '" & commonFunc.FormatDate(VisaExpiry) & "' where Person_ID = " & PersonId) Then
            Dim taskEvents As New TasksEvents()
            taskEvents.GblFunc_Add_Event("Details Changed", EventMessage, 1, PersonId, CommonDataFunctions.EventTypes.EventPerson)
            Return True
        End If

    End Function

    Public Shared Function UpdateVisaType(ByVal PersonId As Long, ByVal VisaId As Integer, ByVal EventMessage As String) As Boolean
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        If DB.ExecuteSQL("Update D_Person set Person_UKVisaID = " & VisaId & " Where Person_ID = " & PersonId) Then
            Dim taskEvents As New TasksEvents()
            taskEvents.GblFunc_Add_Event("Details Changed", EventMessage.Trim, 1, PersonId, CommonDataFunctions.EventTypes.EventPerson)
            Return True
        End If

    End Function

    Public Shared Function GenerateWebPassword(ByVal PersonId As Long) As Boolean

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()
        Dim myData As DataTable
        Dim userName As String
        Dim firstName As String
        Dim lastName As String


        With SQL
            .Append("Select p.person_fname,p.person_lname, p.person_webusername from D_Person P")
            .Append(" Where p.Person_ID=" & PersonId)
        End With
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        myData = DB.GetSQLDataTable(SQL.ToString)

        firstName = myData.Rows(0).Item(0)
        lastName = myData.Rows(0).Item(1)
        userName = IIf(IsDBNull(myData.Rows(0).Item(2)), "", myData.Rows(0).Item(2))

        SQL.Remove(0, SQL.Length)

        With SQL
            .Append("Declare @User as varchar(50) " & vbCrLf)
            .Append(" Declare @PW as varchar(50) " & vbCrLf & vbCrLf)
            .Append(" Exec SP_GenerateWebLogin @Fname = '" & commonFunc.SwapQuotes(firstName.Trim) & "'")
            .Append(" , @Lname = '" & commonFunc.SwapQuotes(lastName.Trim) & "'")
            .Append(" , @username = @User OUTPUT ")
            .Append(" , @password = @pw OUTPUT ")
            .Append(" Select @user as Username, @pw as Password")
        End With

        myData = DB.GetSQLDataTable(SQL.ToString)

        If Not IsNothing(myData) Then
            If myData.Rows.Count > 0 Then

                Dim Pwd As String = myData.Rows(0).Item("password")
                Dim User As String = myData.Rows(0).Item("username")

                SQL = New StringBuilder

                With SQL
                    If userName <> "" Then
                        .Append("Update D_Person set Person_WebPassword = '" & commonFunc.SwapQuotes(Pwd.Trim) & "' ")
                        .Append("Where Person_ID = " & PersonId)
                    Else
                        .Append("Update D_Person set Person_WebPassword = '" & commonFunc.SwapQuotes(Pwd.Trim) & "', ")
                        .Append("person_webusername = '" & commonFunc.SwapQuotes(User.Trim) & "' ")
                        .Append("Where Person_ID = " & PersonId)
                    End If
                End With

                Return DB.ExecuteSQL(SQL.ToString)

            End If
        End If


    End Function

    Public Shared Function HasInvoiceAddress(ByVal PersonId As Long) As Boolean

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()

        With SQL
            .Append("Select count(*) from D_Addresslink ")
            .Append(" Where Addresslink_PersonId = " & PersonId)
            .Append(" And Addresslink_Type in (2,3)")
        End With
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        If DB.GetSQLScalar(SQL.ToString) > 0 Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function GetEmailAddress(ByVal PersonId As String) As String

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()

        With SQL
            .Append("Select EmailAddress_Address from D_EMailAddress ")
            .Append("where EMailAddress_PersonID = " & PersonId & " and EMailAddress_Type = 1")
        End With
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLScalar(SQL.ToString)

    End Function

    Public Shared Function GetPostalAddressLink(ByVal DataId As Long, ByVal AddressLinkType As AddressLinkTypes, ByVal AddressType As AddressTypes) As Long

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()

        With SQL
            .Append("SELECT Addresslink_AddressId FROM D_AddressLink")


            Select Case AddressType

                Case AddressTypes.Company
                    .Append(" WHERE AddressLink_CompanyID = " & DataId)

                Case AddressTypes.Person
                    .Append(" WHERE AddressLink_personID = " & DataId)

            End Select


            Select Case AddressLinkType

                Case People.AddressLinkTypes.Invoice
                    .Append(" AND Addresslink_Type in (2,3)")

                Case People.AddressLinkTypes.Correspondence
                    .Append(" AND Addresslink_Type in (1,3)")

            End Select

        End With
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLScalar(SQL.ToString)

    End Function

    Public Shared Function GetPhoneLink(ByVal DataId As Long, ByVal PhoneType As PhoneTypes, ByVal ClientType As AddressTypes) As Long

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()

        With SQL
            .Append("SELECT PhoneNumber_ID FROM D_Phonenumber")

            Select Case ClientType
                Case AddressTypes.Person
                    .Append(" WHERE Phonenumber_PersonID = " & DataId)
                Case AddressTypes.Company
                    .Append(" WHERE Phonenumber_companyID = " & DataId)
            End Select

            Select Case PhoneType
                Case PhoneTypes.PhoneMain
                    .Append(" AND PhoneNumber_Type in (1,3)")
                Case PhoneTypes.PhoneFax
                    .Append(" AND PhoneNumber_Type in (2,3)")
            End Select

        End With
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLScalar(SQL.ToString)

    End Function

    Public Shared Function GetEmailLink(ByVal DataId As Long) As Long

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()

        With SQL
            .Append("SELECT EMailaddress_ID FROM D_Emailaddress")
            .Append(" WHERE EMailaddress_PersonID = " & DataId)
            .Append(" AND EMailaddress_Type = 1")
        End With
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLScalar(SQL.ToString)

    End Function

    Public Shared Function GetCandidatePartnerLink(ByVal CaseId As Long) As Long

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()

        With SQL
            .Append("SELECT D_Dependants.Dependant_ChildID FROM D_Case INNER JOIN")
            .Append(" D_Dependants ON D_Case.Case_CandidateID = D_Dependants.Dependant_ParentID")
            .Append(" WHERE (D_Case.Case_ID = " & CaseId & ") And (D_Dependants.Dependant_Spouse = 1)")
        End With
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLScalar(SQL.ToString)

    End Function

    Public Shared Function GetCandidateEmailAddress(ByVal CaseId As Long) As String

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()

        With SQL
            .Append("select e.emailaddress_address from d_emailaddress e, d_case c")
            .Append(" where c.case_id = " & CaseId)
            .Append(" and c.case_candidateid = e.emailaddress_personid")
            .Append(" and e.emailaddress_type = 1")
        End With
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLScalar(SQL.ToString)

    End Function

    Public Shared Function GetDependants(ByVal ParentId As Long) As DataTable

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()

        With SQL
            .Append("SELECT D_Person.Person_ID, D_Dependants.Dependant_ChildDesc as Relationship,")
            .Append(" [Person_Title] + ' ' + [Person_FName] + ' ' + [Person_LName] AS Name,")
            .Append(" Case Dependant_Public when 1 then 'Public' else '' end AS Status,")
            .Append(" D_Dependants.Dependant_ID,")
            .Append(" Case D_Dependants.Dependant_Spouse When 1 then 'Partner' else '' end as Spouse")
            .Append(" FROM D_Dependants INNER JOIN D_Person ON D_Dependants.Dependant_ChildID = D_Person.Person_ID ")
            .Append(" WHERE D_Dependants.Dependant_ParentID = " & ParentId)
            .Append(" ORDER BY D_Dependants.Dependant_ID")
        End With
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLDataTable(SQL.ToString)


    End Function

    Public Shared Function GetDependantOf(ByVal ChildId As Long) As DataTable

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()

        With SQL
            .Append("SELECT D_Person.Person_ID, D_Dependants.Dependant_ParentDesc as Relationship, [Person_Title] + ' ' + [Person_FName] + ' ' + [Person_LName] AS Name, Case Dependant_Public when 1 then 'Public' else '' end AS Status ")
            .Append(" FROM D_Dependants INNER JOIN D_Person ON D_Dependants.Dependant_ParentID = D_Person.Person_ID")
            .Append(" WHERE D_Dependants.Dependant_ChildID = " & ChildId)
            .Append(" ORDER BY D_Dependants.Dependant_ID")
        End With
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLDataTable(SQL.ToString)

    End Function

    Public Shared Function GetParentPerson(ByVal ChildId As Long) As ParentInfo

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()
        Dim myData As DataTable

        GetParentPerson = Nothing

        With SQL
            .Append("SELECT D_Person.Person_ID, D_Person.Person_FName + ' ' + D_Person.Person_LName AS Name")
            .Append(" FROM D_Dependants INNER JOIN D_Person ON D_Dependants.Dependant_ParentID = D_Person.Person_ID")
            .Append(" WHERE D_Dependants.Dependant_ChildID = " & ChildId)
        End With
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        myData = DB.GetSQLDataTable(SQL.ToString)

        If Not IsNothing(myData) Then
            If myData.Rows.Count > 0 Then
                Dim myParent As New ParentInfo

                myParent.ParentId = myData.Rows(0).Item("person_id")
                myParent.ParentName = IIf(IsDBNull(myData.Rows(0).Item("name")), "", myData.Rows(0).Item("name"))

                Return myParent
            End If
        End If


    End Function

    Public Shared Function CreateDependant(ByVal ParentId As Long, ByVal ChildId As Long, ByVal RelationshipP As String, ByVal RelationshipC As String, ByVal ConName As String, ByVal ViewPublic As Boolean, ByVal DependantName As String) As Long

        Dim SQL As StringBuilder
        Dim commonFunc As New CommonFuncs()
        Dim DepId As Long
        Dim tasksEvents As New TasksEvents()
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Try
            SQL = New StringBuilder

            With SQL
                .Append("Insert into D_Dependants (Dependant_ParentID, Dependant_ChildID, Dependant_EnteredBy, Dependant_Childdesc, Dependant_Parentdesc, Dependant_Public)")
                .Append(" values(" & ParentId & ", " & ChildId & ", '" & commonFunc.SwapQuotes(ConName.Trim) & "', '" & RelationshipC & "', '" & RelationshipP & "', " & IIf(ViewPublic, 1, 0) & ")")
            End With

            DepId = DB.ExecuteSQL(SQL.ToString, True)

            If DepId > 0 Then
                'DepId = DB.GetIdentity

                tasksEvents.GblFunc_Add_Event("Dependant added", DependantName & " added as a dependant of type " & RelationshipC, tasksEvents.EventStatus.SingleEvent, ParentId, CommonDataFunctions.EventTypes.EventPerson)
                tasksEvents.GblFunc_Add_Event("Set as Dependant", "Set as a dependant of type " & RelationshipC & " to " & GlobalCRM.CurrentCandidateName & " (" & ParentId & ")", tasksEvents.EventStatus.SingleEvent, ChildId, CommonDataFunctions.EventTypes.EventPerson)

                Return DepId
            End If
        Catch ex As Exception
        Finally
            DB = Nothing
            tasksEvents = Nothing
            commonFunc = Nothing
            SQL = Nothing
        End Try

    End Function

    Public Shared Function SetDependantSpouse(ByVal ParentId As Long, ByVal DependantId As Long, ByVal DependantName As String) As Boolean

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()
        Dim Msg As String

        Msg = "Person No. " & DependantId & " (" & DependantName & ") marked as Partner"

        With SQL
            .Append("Update D_Dependants set Dependant_Spouse = ")
            .Append(" Case Dependant_ChildID when " & DependantId & " then 1 else 0 end ")
            .Append(" Where Dependant_ParentID = " & ParentId)
        End With
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        If DB.ExecuteSQL(SQL.ToString) Then
            Dim tasksEvents As New TasksEvents()
            tasksEvents.GblFunc_Add_Event("Details Changed", Msg, 0, ParentId, CommonDataFunctions.EventTypes.EventPerson)
            Return True
        End If

    End Function

    Public Shared Function SetDependantPublicPrivate(ByVal DependantId As Long, ByVal CurrentView As String, ByVal DependantName As String, ByVal ParentId As Long) As Boolean

        Dim SQL As String
        Dim Msg As String

        If CurrentView.ToUpper = "PUBLIC" Then
            SQL = "Update D_Dependants set Dependant_Public = 0 where Dependant_ID = " & DependantId
            Msg = "Dependant " & DependantName.Trim & " made private"
        Else
            SQL = "Update D_Dependants set Dependant_Public = 1 where Dependant_ID = " & DependantId
            Msg = "Dependant " & DependantName.Trim & " made public"
        End If
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        If DB.ExecuteSQL(SQL.ToString) Then
            Dim tasksEvents As New TasksEvents()
            tasksEvents.GblFunc_Add_Event("Details Changed", Msg, tasksEvents.EventStatus.SingleEvent, ParentId, CommonDataFunctions.EventTypes.EventPerson)
            Return True
        End If

    End Function

    Public Shared Function DeleteDependant(ByVal DependantId As Long, ByVal DependantName As String, ByVal ParentId As Long, ByVal ChildId As Long) As Boolean
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim tasksEvents As New TasksEvents()
        If DB.ExecuteSQL("Delete from D_Dependants where dependant_ID = " & DependantId) Then
            tasksEvents.GblFunc_Add_Event("Dependant removed", DependantName & " removed from dependants list", tasksEvents.EventStatus.SingleEvent, ParentId, CommonDataFunctions.EventTypes.EventPerson)
            tasksEvents.GblFunc_Add_Event("Dependancy removed", "Dependancy to " & GlobalCRM.CurrentCandidateName & " removed", tasksEvents.EventStatus.SingleEvent, ChildId, CommonDataFunctions.EventTypes.EventPerson)
            Return True
        End If

    End Function

    Public Shared Function GeneratePassword(ByVal PersonId As Long) As String

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()
        Dim Pwd As String
        Dim dtPerson As New DataTable


        With SQL
            .Append("Select p.person_fname,p.person_lname from D_Person P ")
            .Append(" Where p.Person_ID=" & PersonId)
        End With
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        dtPerson = DB.GetSQLDataTable(SQL.ToString)

        SQL.Remove(0, SQL.Length)
        GeneratePassword = String.Empty

        With SQL
            .Append("Declare @User as varchar(50) " & vbCrLf)
            .Append(" Declare @PW as varchar(50) " & vbCrLf & vbCrLf)
            .Append(" Exec SP_GenerateWebLogin @Fname = '" & dtPerson.Rows(0).Item(0) & "'")
            .Append(" , @Lname = '" & dtPerson.Rows(0).Item(1) & "'")
            .Append(" , @username = @User OUTPUT ")
            .Append(" , @password = @pw OUTPUT ")
            .Append(" Select @pw as Password")
        End With

        Pwd = DB.GetSQLScalar(SQL.ToString)

        If Pwd.Trim = String.Empty Then
            MsgBox("New password not created, please speak to IT", MsgBoxStyle.Critical, "Error")
            Exit Function
        End If

        SQL = New StringBuilder

        With SQL
            .Append("Update D_Person set Person_WebPassword = '" & commonFunc.SwapQuotes(Pwd.Trim) & "'")
            .Append("Where Person_ID = " & PersonId)
        End With

        If DB.ExecuteSQL(SQL.ToString) Then
            Dim tasksEvents As New TasksEvents()
            tasksEvents.GblFunc_Add_Event("Web Password changed", "Web access password changed to " & Pwd, 0, PersonId, CommonDataFunctions.EventTypes.EventPerson)
            Return Pwd
        End If

    End Function

    'Public Shared Function GetContacts(ByVal CompanyId As Long) As DataTable

    '    Dim SQL As New StringBuilder        Dim commonFunc As New CommonFuncs()

    '    With SQL
    '        .Append("Select Person_ID, Person_Fname + ' ' + Person_LName as PersonName")
    '        .Append(" From D_Person INNER JOIN L_Roles ON D_Person.Person_CompanyRoleID = L_Roles.Role_ID")
    '        .Append(" WHERE Person_CompanyID = " & CompanyId)
    '        .Append(" AND Role_Employee in (1,2)")
    '    End With

    '    Return DB.GetSQLDataTable(SQL.ToString)

    'End Function

    Public Shared Function GetPersonEvents(ByVal PersonId As Long) As DataTable

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()

        With SQL
            .Append("SELECT D_Event.Event_ID, Case Event_Category When 2 then Event_Dtscheduled when 3 then Event_Dtcompleted else Event_Dtentered end as EventDT,")
            .Append("D_Event.Event_Description, L_Consultant.con_name, CASE Event_Category WHEN 1 THEN '' WHEN 2 THEN 'Scheduled for ' + convert(varchar(12),")
            .Append("Event_DTScheduled,103) WHEN 3 THEN 'Completed on ' + convert(varchar(12),Event_DTCompleted,103) END AS Category,")
            .Append("Case When Event_LinkType = 1 then 'Case'  when Event_LinkType = 2 then 'Client' end as LinkFor")
            .Append(" FROM D_Event LEFT OUTER JOIN L_Consultant ON D_Event.Event_Enteredby = L_Consultant.Con_ID")
            .Append(" WHERE (D_Event.Event_LinkType = 2) And (D_Event.Event_LinkID = " & PersonId & ")")
            .Append(" Order By Case Event_Category When 2 then Event_Dtscheduled when 3 then Event_Dtcompleted else Event_Dtentered end Desc ")

        End With
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLDataTable(SQL.ToString)

    End Function

    Public Shared Function GetPersonCases(ByVal PersonID As Long) As DataTable

        Dim SQL As New StringBuilder
        Dim commonFunc As New CommonFuncs()

        With SQL
            .Append("Select c.Case_ID as 'ID', prod.Product_Name as 'Product', cs.CaseStage_Title as 'Status', per.Person_FName + ' ' + per.Person_LName as 'Candidate' ")
            .Append("From D_Case c ")
            .Append("Inner Join L_Products prod On c.Case_ProductID = prod.Product_ID ")
            .Append("Inner Join D_Person per On c.Case_CandidateID = per.Person_ID ")
            .Append("Inner Join L_CaseStages cs On c.Case_StatusID = CaseStage_Number ")
            .Append("Where per.Person_ID = " & PersonID)

        End With
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLDataTable(SQL.ToString)

    End Function
    Public Shared Function GetPersonName(ByVal PersonId As Long) As String
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As New StringBuilder
        Try

            With SQL
                .Append("select person_fname + ' ' + person_lname as fullname from d_person ")
                .Append("where person_id = " & PersonId)
            End With

            Return db.GetSQLScalar(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function GetemailAddressFromID(ByVal CandidateID As Long) As String
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As New StringBuilder
        Try

            With SQL
                .Append(" Select dbo.D_EmailAddress.EMailAddress_Address")
                .Append(" FROM         dbo.D_Person INNER JOIN")
                .Append(" dbo.D_EmailAddress ON dbo.D_Person.Person_ID = dbo.D_EmailAddress.EMailAddress_PersonID")
                .Append(" WHERE     (dbo.D_EmailAddress.EMailAddress_Type = 1) AND (dbo.D_Person.Person_ID = " & CandidateID & ")")
            End With

            Return db.GetSQLScalar(SQL.ToString)
        Catch ex As Exception
            Return String.Empty
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function
End Class
